package com.ra.statis;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.annotation.Resource;

import org.apache.commons.lang.time.DateUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.jdbc.ReturningWork;
import org.springframework.stereotype.Service;

import com.da.landlord.DaAgreement;
import com.espirit.eap.manager.SystemLogService;
import com.espirit.eap.manager.TaskSchedulerService;
import com.espirit.eap.sa.LoginUser;
import com.espirit.eap.sa.LoginUserService;
import com.googlecode.cswish.annotation.Rest;
import com.googlecode.cswish.exception.ApplicationException;
import com.googlecode.cswish.model.PageInfo;
import com.googlecode.cswish.model.Reference;
import com.googlecode.cswish.oosearch.QLInfo;
import com.googlecode.cswish.struts.Initialization;
import com.googlecode.cswish.struts.hibernate.HibernateUtil;
import com.googlecode.cswish.struts.spring.BeanIntrospector;
import com.googlecode.cswish.struts.spring.GenericService;
import com.googlecode.cswish.struts.spring.ModelModifyEvent;
import com.googlecode.cswish.struts.spring.SearchCallback;
import com.googlecode.cswish.util.bean.ModelUtil;
import com.opensymphony.xwork2.ActionContext;
import com.ra.crm.CustomerServiceStaff;
import com.ra.crm.SalerStaff;
import com.ra.landlord.Building;
import com.ra.landlord.LandlordAssistUser;
import com.ra.landlord.LandlordOwnerUser;
import com.ra.landlord.LandlordOwnerVIPUser;
import com.ra.landlord.LandlordTracer;
import com.ra.landlord.LandlordUser;
import com.ra.landlord.Room;
import com.ra.landlord.ValidAgreement;
import com.ra.util.Address;
import com.ra.util.AddressCode;
import com.ra.util.AddressCodeService;
import com.ra.util.AreaParser;
import com.ra.util.RaConstant;
import com.ra.util.baidu.BaiduAddress;
import com.ra.util.baidu.BaiduApiUtils;
import com.ra.util.baidu.BaiduPlaceDetail;

@Service
public class LandlordEstimationService implements Initialization {

	private static final Log logger = LogFactory.getLog(LandlordEstimationService.class);
	
	@Resource
	private GenericService genericService;
	
	@Resource
	private TaskSchedulerService taskSchedulerService;
	
	@Resource
	private AddressCodeService addressCodeService;
	
	@Resource
	private LoginUserService loginUserService;
	
	@Resource
	private SystemLogService systemLogService;
	
	@Resource
	private BeanIntrospector beanIntrospector;
	
	@Resource
	private LandlordFunctionService landlordFunctionService;
	
	@SuppressWarnings("unchecked")
	@Override
	public void initSite() {
		for (Class clazz : new Class[] {LandlordUser.class, LandlordAssistUser.class, LandlordOwnerUser.class, LandlordOwnerVIPUser.class}) {
			genericService.on(clazz, GenericService.OPERATION_MERGE, new ModelModifyEvent<LandlordUser>() {
				@Override
				public void onMessage(LandlordUser newModel, LandlordUser oldModel, String operation) {
					if (newModel instanceof LandlordOwnerUser) {
						estimateLandlordUser((LandlordOwnerUser)newModel);
					}
				}
			}, false);
		}
	}
	
	@Override
	public String[] getProductName() {
		return RaConstant.PRODUCT_NAME;
	}


	/**
	 * Scheduled, 房东数据评估, 每晚执行
	 */
	public Integer refresh() {
		// 功能分析
		//landlordFunctionService.analyse();
		
		// import all the LandlordUser id to LandlordEstimation
		int count = genericService.doReturningWork(new ReturningWork<Integer>() {

			@Override
			public Integer execute(Connection connection) throws SQLException {
				int count = 0;
				try (Statement ps = connection.createStatement()) {
					String sql = "insert into ra_landlordestimation(landlordUser_user_id)"
							+ " select a.user_id from ra_landlordpermission a left join ra_landlordestimation b"
							+ " on a.user_id=b.landlordUser_user_id"
							+ " where b.landlordUser_user_id is null and (a.admin_type = 4 or a.admin_type = 6)";
					count = ps.executeUpdate(sql);
				}
				return count;
			}
		});
		
		// update all landlord date 
		// 仅检查激活的或者最近两天有登录的(一天已经足够,两天的目的是为了做一个冗余复查)
		/*List<Integer> ids = genericService.doReturningWork(new ReturningWork<List<Integer>>() {
			@Override
			public List<Integer> execute(Connection connection)
					throws SQLException {
				String sql = "select e.id from ra_landlordestimation e left join ra_landlorduser u on e.landlordUser_user_id = u.user_id"
						+ " left join saas_loginusertracer ut on u.loginUser_id = ut.loginUser_id"
						+ " where ut.loginTime > ? or e.active = 1";
				Calendar cal = Calendar.getInstance();
				cal.add(Calendar.DAY_OF_MONTH, -2);
				cal = DateUtils.truncate(cal, Calendar.DAY_OF_MONTH);
				java.sql.Date last2Day = new java.sql.Date(cal.getTime().getTime());
				List<Integer> ids = new ArrayList<>();
				try (PreparedStatement ps = connection.prepareStatement(sql)) {
					ps.setDate(1, last2Day);
					try (ResultSet rs = ps.executeQuery()) {
						int id = rs.getInt(1);
						ids.add(id);
					}
				}
				return ids;
			}
		});*/
		
		String sql = "select distinct e.* from ra_landlordestimation e left join ra_landlorduser u on e.landlordUser_user_id = u.user_id"
				+ " left join saas_loginusertracer ut on u.loginUser_id = ut.loginUser_id"
				+ " where ut.loginTime > ? or e.active = 1";
		Calendar cal = Calendar.getInstance();
		cal.add(Calendar.DAY_OF_MONTH, -2);
		cal = DateUtils.truncate(cal, Calendar.DAY_OF_MONTH);
		java.sql.Date last2Day = new java.sql.Date(cal.getTime().getTime());
		QLInfo qlInfo = new QLInfo(sql, true, last2Day);
		qlInfo.setNativeSqlResultClass(LandlordEstimation.class);
		List<LandlordEstimation> landlordEstimationList = genericService.searchByQl(qlInfo).getData();
		
		if (logger.isDebugEnabled()) {
			logger.debug("the landlordEstimationList size is " + landlordEstimationList.size() + ", the new count is " + count);
		}
		int size = landlordEstimationList.size();
		int updateCount = 0;
		for (LandlordEstimation landlordEstimation : landlordEstimationList) {
			try {
				if (updateEstimationInfo(landlordEstimation)) {
					genericService.merge(landlordEstimation);
					updateCount++;
				}
			} catch (Exception ex) {
				logger.error("Failed to update estimation,  landlordEstimation.id=" + landlordEstimation.getId(), ex);
				String simpleName = beanIntrospector.getSimpleName(LandlordEstimation.class.getName());
				systemLogService.add(simpleName, "refresh", "id=" + landlordEstimation.getId());
			}
			--size;
			
			if (logger.isDebugEnabled()) {
				if (size % 100 == 0) {
					logger.debug("the rest size is " + size);
				}
			}
		}
		if (logger.isDebugEnabled()) {
			logger.debug(updateCount + " records updated");
		}
		
		// 更新照片和发布房源数
		genericService.doReturningWork(new ReturningWork<Integer>() {

			@Override
			public Integer execute(Connection connection) throws SQLException {
				updatePicCount(connection, null);
				updateRoomInfoCount(connection, null);
				updateQrCodeCount(connection, null);
				updateTenantUserCount(connection, null);
				return 0;
			}
		});
		
		
		taskSchedulerService.updateTaskSchedulerRunTime(this.getClass(), "refresh");
		return count;
	}
	
	private synchronized Integer refreshAndUpdateRecord(Date refreshDay, boolean updateRecord) {
		// import all the LandlordUser id to LandlordEstimation
		int count = genericService.doReturningWork(new ReturningWork<Integer>() {

			@Override
			public Integer execute(Connection connection) throws SQLException {
				int count = 0;
				Calendar today = Calendar.getInstance();
				Calendar todayBegin = DateUtils.truncate(today, Calendar.DAY_OF_MONTH);
				today.add(Calendar.DAY_OF_MONTH, 1);
				Calendar todayEnd = DateUtils.truncate(today, Calendar.DAY_OF_MONTH);
				
				String sql = "insert into ra_landlordestimation(landlordUser_user_id)"
						+ " select l.user_id from (select a.user_id as user_id,u.user_registered as user_registered from ra_landlordpermission a inner join ra_landlorduser u on a.user_id=u.user_id"
						+ " where (a.admin_type = 4 or a.admin_type = 6) ) as l left join ra_landlordestimation b on l.user_id=b.landlordUser_user_id"
						+ " where b.landlordUser_user_id is null and l.user_registered >= ? and l.user_registered < ?";
				try (PreparedStatement ps = connection.prepareStatement(sql)) {
					ps.setDate(1, new java.sql.Date(todayBegin.getTime().getTime()));
					ps.setDate(2, new java.sql.Date(todayEnd.getTime().getTime()));
					count = ps.executeUpdate();
				}
				return count;
			}
		});
		
		int updateCount = 0;
		if (updateRecord) {
			LandlordOwnerUser landlordUser = new LandlordOwnerUser();
			refreshDay = DateUtils.truncate(refreshDay, Calendar.DAY_OF_MONTH);
			landlordUser.setRegister(refreshDay);
			PageInfo<LandlordOwnerUser> pageInfo = genericService.searchAll(landlordUser);
			for (LandlordOwnerUser user : pageInfo.getData()) {
				LandlordEstimation landlordEstimation = new LandlordEstimation();
				landlordEstimation.setLandlordUser(user);
				QLInfo qlInfo = new QLInfo("from " + LandlordEstimation.class.getName() + " a where a.landlordUser.id=?", true);
				qlInfo.addParam(user.getId());
				qlInfo.setMaxResults(1);
				LandlordEstimation dbLandlordEstimation = (LandlordEstimation) genericService.searchByQl(qlInfo).getData(0);
				if (dbLandlordEstimation != null) {
					landlordEstimation = dbLandlordEstimation;
				}
				if (updateEstimationInfo(landlordEstimation)) {
					landlordEstimation = genericService.merge(landlordEstimation);
				}
				
				// 更新照片和发布房源数
				final Integer theLandlordUserId = landlordEstimation.getLandlordUser().getId();
				genericService.doReturningWork(new ReturningWork<Integer>() {

					@Override
					public Integer execute(Connection connection) throws SQLException {
						updatePicCount(connection, theLandlordUserId);
						updateRoomInfoCount(connection, theLandlordUserId);
						// theLandlordUserId --> building id list
						// updateQrCodeCount(connection, null);
						updateTenantUserCount(connection, theLandlordUserId);
						return 0;
					}
				});
			}
			updateCount = pageInfo.getData().size();
		}
		return updateCount;
	}
	
	// 销售入口
	public <T> PageInfo<T> searchFunction(T model, final boolean fromMenu,
			boolean refresh, final boolean unphone,
			int start, int limit, String sort, String dir, boolean oldSearch,
			Reference<QLInfo> qlInfoRef) {
		return search(model, fromMenu, refresh, unphone, start, limit, sort, dir, oldSearch, qlInfoRef);
	}
	
	// 客服入口
	public <T> PageInfo<T> searchEstimation(T model, final boolean fromMenu,
			boolean refresh, final boolean unphone,
			int start, int limit, String sort, String dir, boolean oldSearch,
			Reference<QLInfo> qlInfoRef) {
		return search(model, fromMenu, refresh, unphone, start, limit, sort, dir, oldSearch, qlInfoRef);
	}

	public <T> PageInfo<T> search(final T model, final boolean fromMenu,
			boolean refresh, final boolean unphone,
			int start, int limit, String sort, String dir, boolean oldSearch,
			Reference<QLInfo> qlInfoRef) {
		Object register = ModelUtil.getProperty(model, "landlordUser.register");
		if (register != null && register instanceof Date) {
			boolean updateRecord = refresh;
			refreshAndUpdateRecord((Date) register, updateRecord);
		}
		if (!oldSearch) {
			Integer id = (Integer) ModelUtil.getProperty(model, "landlordUser.detailAddress.addressCode.id");
			if (id != null) {
				Address address = (Address) ModelUtil.getProperty(model, "landlordUser.detailAddress");
				AddressCode addressCode = genericService.load(AddressCode.class, id);
				Integer district = addressCodeService.getCodeByLevel(addressCode, AddressCode.LEVEL_DISTRICT);
				Integer city = addressCodeService.getCodeByLevel(addressCode, AddressCode.LEVEL_CITY);
				Integer province = addressCodeService.getCodeByLevel(addressCode, AddressCode.LEVEL_PROVINCE);
				
				address.setProvince(province);
				address.setCity(city);
				address.setDistrict(district);
				address.setAddressCode(null);
			} else {
				String[] fullName = (String[]) ModelUtil.getProperty(model, "landlordUser.detailAddress.fullName");
				if (fullName != null && fullName.length > 0) {
					String[] codes = AreaParser.findAreaCode(fullName[0]);
					if (codes != null) {
						if (codes[0] != null) {
							ModelUtil.setProperty(model, "landlordUser.detailAddress.province", codes[0]);
						}
						if (codes[1] != null) {
							ModelUtil.setProperty(model, "landlordUser.detailAddress.city", codes[1]);
						}
						if (codes[2] != null) {
							ModelUtil.setProperty(model, "landlordUser.detailAddress.district", codes[2]);
						}
					}
				}
			}
		}
		
		// 特许情况: 如果输入了正确的电话号码, 可以直接查询, 无需过滤
		String phone = (String)ModelUtil.getProperty(model, "landlordUser.phone");
		PageInfo<T> pageInfo = null;
		if (StringUtils.isNotEmpty(phone)) {
			LandlordEstimation estimation = searchLandlordByPhone(phone);
			if (estimation != null) {
				pageInfo = new PageInfo<T>();
				List data = new ArrayList(1);
				data.add(estimation);
				pageInfo.setData(data);
			}
		}
		
		if (pageInfo == null) {
			// 分配后的激活用户
			SearchCallback callback = new SearchCallback() {
				@Override
				public QLInfo afterBuildQL(boolean newSearch, QLInfo qlInfo) {
					boolean onlyTodayRecord = fromMenu;
					LoginUser loginUser = loginUserService.getLoginUser();
					// hard code the data permission
					if (loginUser != null) {
						LoginUser user = genericService.load(loginUser);
						if (user instanceof SalerStaff) {
							onlyTodayRecord = false;
							qlInfo.andClause("(a.saler=? or a.saler2=?)");
							qlInfo.addParam(user);
							qlInfo.addParam(user);
						} else if (user instanceof CustomerServiceStaff) {
	//						ModelUtil.setProperty(model, "customerService", user);
	//						onlyTodayRecord = false;
						}
					}
					
					if (onlyTodayRecord) {
						Calendar today = Calendar.getInstance();
						today = DateUtils.truncate(today, Calendar.DAY_OF_MONTH);
						ModelUtil.setProperty(model, "landlordUser.register", today.getTime());
					}
					
					if (newSearch) {
	//					if (reActive) {		// 分配维护人员后, 重新激活的房东
	//						String where = "a.assignDate is not null and a.modifyOn > a.assignDate and a.active=true";
	//						qlInfo.andClause(where);
	//					}
						if (unphone) {
							qlInfo.andClause("a.phoneDate is null");
						}
					}
					return qlInfo;
				}
			};
			pageInfo = genericService.search(model, start, limit, sort, dir, oldSearch, qlInfoRef, callback, true, false);
		}
		return pageInfo;
	}
	
	public LandlordEstimation _phone(LandlordEstimation model, LandlordTracer tracer) {
		if (model.getPhoneDate() != null) {
			ActionContext.getContext().getValueStack().setValue("phoneStatus", true);
		}
		
		LandlordEstimation landlordEstimation;
		if (model.getId() == null && model.getLandlordUser() != null) {
			Integer landlordUserId = model.getLandlordUser().getId();
			if (landlordUserId != null) {
				landlordEstimation = genericService.searchAll(model).getData(0);
				ModelUtil.copy(landlordEstimation, model);
			} else {
				landlordEstimation = null;
			}
		} else {
			landlordEstimation = genericService.view(model);
		}
		updateEstimationInfo(model);
		return landlordEstimation;
	}
	
	@Rest(type="search")
	public LandlordEstimation view(LandlordEstimation model, LandlordTracer tracer) {
		return _phone(model, tracer);
	}
	
	public LandlordEstimation phone(LandlordEstimation model, String newMarkup, boolean phoneStatus) {
		if (model.getFeedbackType() == null) {
			throw new ApplicationException("用户反馈是必填字段");
		}
		
		// fix the customer information
		LandlordEstimation dbLandlordEstimation = genericService.load(model);
		if (dbLandlordEstimation.getCustomerService() == null) {
			LoginUser loginUser = loginUserService.getLoginUser();
			LoginUser dbLoginUser = genericService.load(loginUser);
			if (dbLoginUser instanceof CustomerServiceStaff) {
				model.setCustomerService((CustomerServiceStaff)dbLoginUser);
				genericService.merge(dbLandlordEstimation);
			}
		}
		
		// update the phone status
		if (!LandlordEstimation.FEEDBACK_TYPE_9.equals(model.getFeedbackType())) {		// 电话无人接听
			model.setPhoneDate(Calendar.getInstance().getTime());
		}
		if (StringUtils.isNotEmpty(newMarkup)) {
			String today = '【' + new SimpleDateFormat("yyyy-MM-dd HH:mm").format(Calendar.getInstance().getTime()) + "】 ";
			
			if (StringUtils.isNotEmpty(dbLandlordEstimation.getMarkup())) {
				model.setMarkup(dbLandlordEstimation.getMarkup() + "\n" + today + newMarkup);
			} else {
				model.setMarkup(today + newMarkup);
			}
		}
		LandlordEstimation landlordEstimation = genericService.update(model);
		return landlordEstimation;
	}
	
	private int getLoginCount(Integer landlordUserId) {
		if (landlordUserId == null) {
			return 0;
		}
		
		String ql = "from " + LandlordTracer.class.getName() + " a where a.landlordUser.id=?";
		int count = genericService.getCount(new QLInfo(ql, true, landlordUserId), false);
		return count;
	}
	
	private boolean usePC(Integer landlordUserId) {
		if (landlordUserId == null) {
			return false;
		}
		
		String ql = "from " + LandlordTracer.class.getName() + " a where a.landlordUser.id=? and a.computer!=?";
		QLInfo qlInfo = new QLInfo(ql, true, landlordUserId, "APP");
		qlInfo.setMaxResults(1);
		boolean hasPc = genericService.searchByQl(qlInfo).getData(0) != null;
		return hasPc;
	}
	
	private boolean useAPP(Integer landlordUserId) {
		if (landlordUserId == null) {
			return false;
		}
		
		String ql = "from " + LandlordTracer.class.getName() + " a where a.landlordUser.id=? and a.computer=?";
		QLInfo qlInfo = new QLInfo(ql, true, landlordUserId, "APP");
		qlInfo.setMaxResults(1);
		boolean hasAPP = genericService.searchByQl(qlInfo).getData(0) != null;
		return hasAPP;
	}
	
	public LandlordEstimation update(LandlordEstimation model) {
		LandlordUser landlordUser = genericService.load(model).getLandlordUser();
		model.setLandlordUser(landlordUser);
		// 更新的同时, 会重新评估
		updateEstimationInfo(model);
		// 分配的时间
		model.setAssignDate(Calendar.getInstance().getTime());
		return genericService.update(model);
	}
	
	@Rest(type="_update")
	public void _updateList(List<LandlordEstimation> results, LandlordEstimation model) {
		for (LandlordEstimation item : results) {
			if (item.getId() != null) {
				LandlordEstimation dbLandlordEstimation = genericService.load(item);
				ModelUtil.copy(dbLandlordEstimation, item);
			}
		}
	}
	
	@Rest(type="_update")
	public void updateList(List<LandlordEstimation> results, LandlordEstimation model) {
		for (LandlordEstimation item : results) {
			if (item.getId() != null) {
				ModelUtil.copyIgnoreNullValue(model, item);
				update(item);
			}
		}
	}
	
	private void estimateLandlordUser(LandlordOwnerUser model) {
		String ql = "from " + LandlordEstimation.class.getName() + " a where a.landlordUser.id=?";
		LandlordEstimation landlordEstimation = (LandlordEstimation) genericService.searchByQl(new QLInfo(ql, true, model.getId())).getData(0);
		if (landlordEstimation == null) {
			landlordEstimation = new LandlordEstimation();
			landlordEstimation.setLandlordUser(model);
			//genericService.merge(landlordEstimation);
		}
		if (updateEstimationInfo(landlordEstimation)) {
			genericService.merge(landlordEstimation);
		}
	}
	
	private boolean updateEstimationInfo(LandlordEstimation model) {
		int scope = 0;
		boolean active;
		// building count
		int buildingCount = -1;
		
		// Fix landlord address
		LandlordUser landlordUser = model.getLandlordUser();
		if (landlordUser == null || !HibernateUtil.isValid(landlordUser, "phone")) {
			logger.error("Invalid LandlordUser, LandlordEstimation.id = " + model.getId());
			return false;
		}
		
		LandlordUser dbLandlordUser = null;
		if (landlordUser != null) {
			dbLandlordUser = genericService.load(landlordUser);		// Load Db model
		}
		if (landlordUser.getDetailAddress() == null) {
			if (dbLandlordUser != null) {
				// use the first building address as landlord address
				String name = dbLandlordUser.getAddress();
				if (StringUtils.isNotEmpty(name)) {
					PageInfo<BaiduPlaceDetail> pageInfo = BaiduApiUtils.searchPlaceByKeyword(name, null, null, 1, 1);
					if (pageInfo != null) {
						BaiduPlaceDetail detail = pageInfo.getData(0);
						if (detail != null) {
							Address address = addressCodeService.convertBaiduAddress(detail);
							if (address.getProvince() != null && address.getCity() != null) {
								dbLandlordUser.setDetailAddress(address);
							}
						}
					}
				}
	
				// find from the building
				if (dbLandlordUser.getDetailAddress() == null) {
					if (dbLandlordUser.getBuildings() != null) {
						buildingCount = dbLandlordUser.getBuildings().size();
						for (Building building : dbLandlordUser.getBuildings()) {
							if (building.getAddress() != null) {
								dbLandlordUser.setDetailAddress(building.getAddress());
								break;
							}
						}
					}
				}
				
				if (dbLandlordUser.getDetailAddress() != null) {
					dbLandlordUser = genericService.merge(dbLandlordUser);
				}
			} else {
				logger.error("Invalid landlord user id: " + landlordUser.getId());
				return false;
			}
		}
		
		Integer landlordUserId = landlordUser.getId();
		if (buildingCount == -1) {
			String ql = "from " + Building.class.getName() + " a where a.landlordUser.id=?";
			buildingCount = genericService.getCount(new QLInfo(ql, true, landlordUserId), false);
		}
		
		// room count
		String ql = "from " + Room.class.getName() + " a where a.landlordUser.id=?";
		int roomCount = genericService.getCount(new QLInfo(ql, true, landlordUserId), false);
		
		// dasuite count
		ql = "from com.da.landlord.DaSuite a where a.landlordUser.id=?";
		int suiteCount = genericService.getCount(new QLInfo(ql, true, landlordUserId), false);
		
		// suite room count
		ql = "from com.da.landlord.DaRoom a where a.landlordUser.id=?";
		int suiteRoomCount = genericService.getCount(new QLInfo(ql, true, landlordUserId), false);
		
		// 房源评分
		if (roomCount >= 200 || suiteRoomCount >= 20) {
			scope = 5;
		} else if (roomCount >= 100 || suiteRoomCount >= 10) {
			scope = 4;
		} else if (roomCount >= 20 || suiteRoomCount >= 2) {
			scope = 3;
		} else if (roomCount >= 5 || suiteRoomCount >= 1) {
			scope = 2;
		} else {
			if (buildingCount > 0 || roomCount > 0) {
				scope = 1;
			}
		}
		
		// agreement count
		ql = "from " + ValidAgreement.class.getName() + " a where a.landlordUser.id=?";
		int agreementCount = genericService.getCount(new QLInfo(ql, true, model.getLandlordUser().getId()), false);
		
		ql = "from " + DaAgreement.class.getName() + " a where a.landlordUser.id=? and a.agreementState=0";
		agreementCount += genericService.getCount(new QLInfo(ql, true, model.getLandlordUser().getId()), false);
		
		// login count, last 2 month
		ql = "from " + LandlordTracer.class.getName() + " a where a.landlordUser.id=? and a.loginTime>?";
		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.MONTH, -2);
		int loginCountOf2Month = genericService.getCount(new QLInfo(ql, true, model.getLandlordUser().getId(), calendar.getTime()), false);
		int gapDays = 0;
		if (model.getLastLoginTime() != null && dbLandlordUser != null && dbLandlordUser.getRegister() != null) {
			long gap = model.getLastLoginTime().getTime() - dbLandlordUser.getRegister().getTime();
			gapDays = (int)(gap / 1000 / 3600 / 24);
		}
		// 条件1: 最近有频繁登录(排除当天的用户)
		// 条件2: 合约数大于10, 并且最近有登录过
		active = loginCountOf2Month > 10 && gapDays > 0 || agreementCount > 10 && loginCountOf2Month >= 1;
		
		// TODO: pic count
		// ql = "from " + RoomAttachment.class.getName() + " a where a.room.landlordUser.id=?";
		//int picCount = genericService.getCount(new QLInfo(ql, true, model.getLandlordUser().getId()), false);
		
		// TODO: roomInfo count?
		boolean changed = false;
		if (changed(agreementCount, model.getAgreementCount())) {
			model.setAgreementCount(agreementCount);
			changed = true;
		}
		
		if (changed(scope, model.getScope())) {
			model.setScope(scope);
			changed = true;
		}
		if (changed(active, model.getActive())) { 
			model.setActive(active);
			model.setModifyOn(Calendar.getInstance().getTime());
			changed = true;
		}
		if (changed(buildingCount, model.getBuildingCount())) {
			model.setBuildingCount(buildingCount);
			changed = true;
		}
		if (changed(roomCount, model.getRoomCount())) {
			model.setRoomCount(roomCount);
			changed = true;
		}
		if (changed(suiteCount, model.getSuiteCount())) {
			model.setSuiteCount(suiteCount);
			changed = true;
		}
		if (changed(suiteRoomCount, model.getSuiteRoomCount())) {
			model.setSuiteRoomCount(suiteCount);
			changed = true;
		}
		boolean useApp = useAPP(landlordUserId);
		if (changed(useApp, model.getUseAPP())) {
			model.setUseAPP(useApp);
			changed = true;
		}
		boolean usePc = usePC(landlordUserId);
		if (changed(usePc, model.getUsePC())) {
			model.setUsePC(usePc);
			changed = true;
		}
		int loginCount = getLoginCount(landlordUserId);
		if (changed(loginCount, model.getLoginCount())) {
			model.setLoginCount(loginCount);
			changed = true;
		}
		boolean bindCard = false;
		if (dbLandlordUser != null && !dbLandlordUser.getBankCards().isEmpty()) {
			bindCard = true;
		}
		if (changed(bindCard, model.getBindBankCard())) {
			model.setBindBankCard(bindCard);
			changed = true;
		}
		
		ql = "from " + LandlordTracer.class.getName() + " a where a.landlordUser.id=? order by a.loginTime desc";
		QLInfo qlInfo = new QLInfo(ql, true, model.getLandlordUser().getId());
		qlInfo.setMaxResults(1);
		LandlordTracer dbLandlordTracer = (LandlordTracer) genericService.searchByQl(qlInfo).getData(0);
		if (dbLandlordTracer != null) {
			if (changed(dbLandlordTracer.getLoginTime(), model.getLastLoginTime())) {
				model.setLastLoginTime(dbLandlordTracer.getLoginTime());
				changed = true;
			}
			
			if (landlordUser.getDetailAddress() == null) {
				// update address by the ip
				BaiduAddress baiduAddress = BaiduApiUtils.ipLocation(dbLandlordTracer.getIp());
				if (baiduAddress != null) {
					Address address = addressCodeService.convertBaiduAddress(baiduAddress);
					if (address.getProvince() != null && address.getCity() != null) {
						address.setFromIp(true);
						landlordUser.setDetailAddress(address);
					}
					
					if (landlordUser.getDetailAddress() != null) {
						landlordUser = genericService.merge(landlordUser);
						changed = true;
					}
				}
			}
		}
		
		// 
		
		return changed;
	}
	
	private void updatePicCount(Connection connection, Integer landlordUserId) throws SQLException {
		String picSql1 = "select count(a.id), r.createuserid from ra_roomattachment a left join ra_room r on a.room_room_id = r.room_id"
				+ (landlordUserId == null ? " group by r.createuserid" : " where r.createuserid=" + landlordUserId);
		String picSql2 = "select count(da.id), s.landlordUser_user_id from da_dasuiteattachment da left join da_dasuite s on da.suite_id = s.id"
				+ (landlordUserId == null ? " group by s.landlordUser_user_id" : " where s.landlordUser_user_id=" + landlordUserId);
		
		updatePicCount(connection, picSql1, landlordUserId, false);
		updatePicCount(connection, picSql2, landlordUserId, true);
	}

	private void updatePicCount(Connection connection, String picSql, Integer landlordUserId, boolean isAdd) throws SQLException {
		try (PreparedStatement stmt = connection.prepareStatement(picSql)) {
			try (ResultSet rs = stmt.executeQuery()) {
				while (rs.next()) {
					Integer picCount = rs.getInt(1);
					Integer landlordId = rs.getInt(2);
					if (landlordId == 0) {
						landlordId = landlordUserId;
					}
					
					LandlordEstimation example = new LandlordEstimation();
					LandlordUser landlordUser = new LandlordUser();
					landlordUser.setId(landlordId);
					example.setLandlordUser(landlordUser);
					LandlordEstimation dbLandlordEstimation = genericService.searchAll(example).getData(0);
					if (dbLandlordEstimation == null) {
						dbLandlordEstimation = new LandlordEstimation();
						dbLandlordEstimation.setLandlordUser(landlordUser);
					}
					
					Integer oldCount = dbLandlordEstimation.getPictureCount();
					if (isAdd && oldCount != null) {
						dbLandlordEstimation.setPictureCount(oldCount + picCount);
					} else {
						dbLandlordEstimation.setPictureCount(picCount);
					}
					boolean changed = !dbLandlordEstimation.getPictureCount().equals(oldCount);
					if (changed) {
						genericService.merge(dbLandlordEstimation);
					}
				}
			}
		}
	}
	
	private void updateRoomInfoCount(Connection connection, Integer landlordUserId) throws SQLException {
		String sql = "select count(ri.id), ri.landlordUser_user_id from ra_roominfo ri where ri.status=1"
				+ (landlordUserId == null ? " group by ri.landlordUser_user_id" : " and ri.landlordUser_user_id=" + landlordUserId);
		try (PreparedStatement stmt = connection.prepareStatement(sql)) {
			try (ResultSet rs = stmt.executeQuery()) {
				while (rs.next()) {
					Integer publishCount = rs.getInt(1);
					Integer landlordId = rs.getInt(2);
					if (landlordId == 0) {
						landlordId = landlordUserId;
					}
					
					LandlordEstimation example = new LandlordEstimation();
					LandlordUser landlordUser = new LandlordUser();
					landlordUser.setId(landlordId);
					example.setLandlordUser(landlordUser);
					LandlordEstimation dbLandlordEstimation = genericService.searchAll(example).getData(0);
					if (dbLandlordEstimation == null) {
						dbLandlordEstimation = new LandlordEstimation();
						landlordUser = genericService.load(landlordUser);
						dbLandlordEstimation.setLandlordUser(landlordUser);
					}
					
					Integer oldCount = dbLandlordEstimation.getPublishCount();
					dbLandlordEstimation.setPublishCount(publishCount);
					boolean changed = !dbLandlordEstimation.getPublishCount().equals(oldCount);
					if (changed) {
						genericService.merge(dbLandlordEstimation);
					}
				}
			}
		}
	}
	
	private void updateTenantUserCount(Connection connection, Integer landlordUserId) throws SQLException {
		String sql = "select count(*), createuserid from ra_tenantuser u where data_status = 1"
				+ (landlordUserId == null ? " group by createuserid" : " and createuserid=" + landlordUserId);
		try (PreparedStatement stmt = connection.prepareStatement(sql)) {
			try (ResultSet rs = stmt.executeQuery()) {
				while (rs.next()) {
					Integer tenantUserCount = rs.getInt(1);
					Integer landlordId = rs.getInt(2);
					if (landlordId == 0) {
						landlordId = landlordUserId;
					}
					
					LandlordEstimation example = new LandlordEstimation();
					LandlordUser landlordUser = new LandlordUser();
					landlordUser.setId(landlordId);
					example.setLandlordUser(landlordUser);
					LandlordEstimation dbLandlordEstimation = genericService.searchAll(example).getData(0);
					if (dbLandlordEstimation == null) {
						dbLandlordEstimation = new LandlordEstimation();
						landlordUser = genericService.load(landlordUser);
						dbLandlordEstimation.setLandlordUser(landlordUser);
					}
					
					Integer oldCount = dbLandlordEstimation.getWeixinUserCount();
					dbLandlordEstimation.setWeixinUserCount(tenantUserCount);
					boolean changed = !dbLandlordEstimation.getWeixinUserCount().equals(oldCount);
					if (changed) {
						genericService.merge(dbLandlordEstimation);
					}
				}
			}
		}
	}
	
	
	private void updateQrCodeCount(Connection connection, Integer userBuildingId) throws SQLException {
		String sql = "select count(w.wx_user_id), w.sourceParameter from ra_weixinuser w where w.sourceFrom=11 and w.subscribe=1"
				+ (userBuildingId == null ? " group by w.sourceParameter" : " and w.sourceParameter='" + userBuildingId + "'");
		Map<Integer, Integer> landlordUserCount = new HashMap<>();
		try (PreparedStatement stmt = connection.prepareStatement(sql)) {
			try (ResultSet rs = stmt.executeQuery()) {
				while (rs.next()) {
					Integer publishCount = rs.getInt(1);
					String sourceParameter = rs.getString(2);
					Integer buildingId = new Integer(sourceParameter);
					
					Building building = genericService.load(Building.class, buildingId);
					if (building != null && building.getLandlordUser() != null) {
						Integer landlordUserId = building.getLandlordUser().getId();
						Integer oldCount = landlordUserCount.get(landlordUserId);
						if (oldCount == null) {
							landlordUserCount.put(landlordUserId, publishCount);
						} else {
							landlordUserCount.put(landlordUserId, oldCount + publishCount);
						}
					}
				}
			}
		}
		
		for (Entry<Integer, Integer> entry : landlordUserCount.entrySet()) {
			Integer landlordId = entry.getKey();
			Integer publishCount = entry.getValue();
			
			LandlordEstimation example = new LandlordEstimation();
			LandlordUser landlordUser = new LandlordUser();
			landlordUser.setId(landlordId);
			example.setLandlordUser(landlordUser);
			LandlordEstimation dbLandlordEstimation = genericService.searchAll(example).getData(0);
			if (dbLandlordEstimation == null) {
				dbLandlordEstimation = new LandlordEstimation();
				landlordUser = genericService.load(landlordUser);
				dbLandlordEstimation.setLandlordUser(landlordUser);
			}
			
			Integer oldCount = dbLandlordEstimation.getQrCodeCount();
			dbLandlordEstimation.setQrCodeCount(publishCount);
			boolean changed = !dbLandlordEstimation.getQrCodeCount().equals(oldCount);
			if (changed) {
				genericService.merge(dbLandlordEstimation);
			}
		}
	}
	
	private boolean changed(Date v1, Date v2) {
		return !(v1 == null && v2 == null || v1 != null && v1.equals(v2));
	}
	private boolean changed(Integer v1, Integer v2) {
		return !(v1 == null && v2 == null || v1 != null && v1.equals(v2));
	}
	private boolean changed(Boolean v1, Boolean v2) {
		return !(v1 == null && v2 == null || v1 != null && v1.equals(v2));
	}
	
	
	public LandlordEstimation searchLandlordByPhone(String phone) {
		if (StringUtils.isEmpty(phone)) {
			throw new ApplicationException("请输入电话号码");
		}
		
		LandlordUser landlordUser = new LandlordUser();
		landlordUser.setPhone(phone);
		LandlordUser dbLandlordUser = genericService.searchAll(landlordUser).getData(0);
		
		LandlordEstimation estimation = null;
		if (dbLandlordUser != null) {
			LandlordEstimation landlordEstimation = new LandlordEstimation();
			landlordEstimation.setLandlordUser(dbLandlordUser);
			LandlordEstimation dbLandlordEstimation = genericService.searchAll(landlordEstimation).getData(0);
			
			if (dbLandlordEstimation == null) {
				if (updateEstimationInfo(landlordEstimation)) {
					dbLandlordEstimation = genericService.merge(landlordEstimation);
				}
			}
			estimation = dbLandlordEstimation;
		}
		return estimation;
	}
	
	public String fixVcData(List<LandlordEstimation> results) {
		StringBuilder sb = new StringBuilder();
		for (LandlordEstimation model : results) {
			if (model != null) {
				LandlordEstimation dbLandlordEstimation = genericService.load(model);
				String msg = fixVcData(dbLandlordEstimation.getLandlordUser().getId());
				if (!msg.isEmpty()) {
					sb.append(msg);
				}
			}
		}
		
		if (sb.length() == 0) {
			return "没有需要修复的数据";
		} else {
			return sb.toString();
		}
	}
	
	public String fixVcData(final Integer landlordUserId) {
		String data = genericService.doReturningWork(new ReturningWork<String>() {

			@Override
			public String execute(Connection connection) throws SQLException {
				StringBuilder info = new StringBuilder();
				
				List<String> sqls = Arrays.asList("select agreement_id,net_ip,deposit,rent_pay,book_pay,rent_period,rent_begin_date,rent_end_date,rent_leave_date,return_fee,mode_dt,change_info,remark,rent_growth_money from ra_agreement where createuserid=?",
						"select AgreementRentId,rent_pay,BeginDate from ra_agreementrent where createuserid=?",
						"select floor_id,floor_name from ra_buildingfloor where createuserid=?",
						"select typespecification_id,typespecification_content,typespecification_Name,typespecification_remark from ra_dictionary where createuserid=?",
						"select feeid,amount_recv,amount_actual,date_begin_recv,date_end_recv,date_actual,balance,rental,remark from ra_fee where createuserid=?",
						"select feeid,date_actual,date_begin_recv,date_end_recv,remark from ra_fee_record where createuserid=?",
						"select FeeAdditionId,price_actual,price_recv,date_actual from ra_feeaddition where createuserid=?",
						"select sytbFeeItemCalcId,Description,price,valuehigh,valuelow from ra_feerulecfgcalc where createuserid=?",
						"select FeeItemDescId,FeeItemDescName,Description,initial_value,Loss_value,Magnification,Calcmode,unit from ra_feerulecfgdesc where createuserid=?",
						"select Mail_Index,Server_Addr,Recv_Addr,Sender_Addr,Mail_Caption,Mail_Description,Send_Timer from ra_mail where createuserid=?",
						"select MeterId,MeterCount,price from ra_meter where createuserid=?",
						"select id,content,createTime from ra_notetemplate where createuserid=?",
						"select notebook_id,notebook_content,notebook_price,notebook_time_type,notebook_interval_day,notebook_interval_time from ra_payrecord where createuserid=?",
						"select recver_id,strName,strNum,strRemark from ra_receivedmessage where createuserid=?",
						"select feeid,date_actual,date_begin_recv,date_end_recv,remark from ra_rent_order_fee where createuserid=?",
						"select room_id,room_num,room_name,rental,manage_fee,clean_fee,net_fee,def1_fee_name,def1_fee,def2_fee_name,def2_fee,strRemark from ra_room where createuserid=?",
						"select FigurationId,Figurationname,Remarks,Number,Acquisition_time,Allocation_time,Brand_specifications,Prize from ra_roomitemcfg where createuserid=?",
						"select id,contactsName,serviceDescribe,certificate,name,phone,remark,picture,pictureFileName from ra_serviceinfo where createuserid=?",
						"select iShortMsgId,strSendDateTime,strParam1,strParam2,strMsgContent from ra_sms where createuserid=?",
						"select iShortMsgId,strTitle,strOutline,param_lparam,strFlag from ra_smsrule where createuserid=?",
						"select user_id,password,photo_file_name,user_name,user_id_card,user_aliase,user_e_mail,user_phone,user_certificate,user_telephone,user_qq,user_address,idCard from ra_tenantuser where createuserid=?",
						"select worklog_id,worklog_title,worklog_content from ra_worklog where createuserid=?");
				
				/*
				sqls = new ArrayList<String>();
				DatabaseMetaData dbmd = connection.getMetaData();
				try (ResultSet rs = dbmd.getTables(null, null, "ra_%", new String[]{"TABLE"})) {
					while(rs.next()) {
						String tableName = rs.getString("TABLE_NAME");
						
						boolean isLandlordUserData = false;
						List<String> columnNames = new ArrayList<String>();
						String pk = null;
						
						try (ResultSet pkRs = dbmd.getPrimaryKeys(null, null, tableName)) {
							while (pkRs.next()) {
								pk = pkRs.getString("COLUMN_NAME");
							}
						}
						
						try (ResultSet columnRs = dbmd.getColumns(null, null, tableName, null)) {
							while (columnRs.next()) {
								int type = columnRs.getInt("DATA_TYPE");
								String columnName = columnRs.getString("COLUMN_NAME");
								
								// createuserid
								if (type == Types.CHAR || type == Types.VARCHAR) {
									columnNames.add(columnName);
								}
								if ("createuserid".equals(columnName)) {
									isLandlordUserData = true;
								}
							}
						}
						if (isLandlordUserData && !columnNames.isEmpty()) {
							String sql = "select " + pk + "," + StringUtils.join(columnNames, ",") + " from " + tableName + " where createuserid=?";
							sqls.add(sql);
							
							System.out.println('"' + sql + "\",");
						}
					}
				}*/
				
				for (String sql : sqls) {
					int tableBegin = sql.indexOf(" from ") + 6;
					int tableEnd = sql.indexOf(' ', tableBegin);
					String tableName = sql.substring(tableBegin, tableEnd);
					int begin = sql.indexOf(' ') + 1;
					int end = sql.indexOf(',');
					String pkName = sql.substring(begin, end);
					try (PreparedStatement ps = connection.prepareStatement(sql)) {
						ps.setInt(1, landlordUserId);
						
						try (ResultSet rs = ps.executeQuery()) {
							while (rs.next()) {
								int index = 1;
								int columnCount = rs.getMetaData().getColumnCount();
								Integer pk = rs.getInt(1);
								while (index < columnCount) {
									String str = rs.getString(++index);
									
									if (str != null && !str.isEmpty()) {
										if (str.indexOf('\\') != -1) {
											// fix data
											info.append(tableName).append(";").append(pkName).append('=').append(pk).append(";【").append(str).append("】\n");
										}
									}
								}
							}
						}
						
					}
				}
				
				return info.toString();
			}
		});
		return data;
	}
}